package com.study.samsung.Dao;

import com.study.samsung.Entity.Commodity;
import com.study.samsung.Entity.Succession;
import com.study.samsung.Utils.Pager;
import com.study.samsung.Utils.RowMapper;
import com.study.samsung.Utils.SQLHelper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodsListDao {
    SQLHelper sqlHelper = new SQLHelper();
    public List<Succession> selectxl() {
        String sql = "select * from succession";
        return sqlHelper.query(sql, new SuccessionRowmapper());
    }
    public Pager flquery(String[] arr,Integer pageno) {
        StringBuilder stringBuilder=new StringBuilder();
        StringBuilder s=new StringBuilder();
        List<Object> params = new ArrayList<>();
        s.append("select count(*) from commodity a left join classification c on a.lid = c.lid and isdefault=1 ");
        stringBuilder.append("select a.* from commodity a left join classification c on a.lid = c.lid and isdefault=1 ");

        for (int i = 0; i < arr.length; i++) {
            if (i==0){
                stringBuilder.append(" where c.xid = ?");
                s.append("  where c.xid = ?");
            }else{
                stringBuilder.append(" or c.xid = ?");
                s.append(" or c.xid = ?");
            }
            params.add(arr[i]);
        }

        Integer total = sqlHelper.one(s.toString(),rs -> {
            return rs.getInt(1);
        },params.toArray());
        stringBuilder.append(" limit ? , ?");
        params.add((pageno-1)*16);
        params.add(16);
        List<Commodity> query = sqlHelper.query(stringBuilder.toString(), new CommodityRowmapper(), params.toArray());

        Pager pager = new Pager(pageno,16);
        pager.setData(query);
        pager.setTotal(total);
        return pager;
    }
    public Integer getTol(String xid,Integer pageNo){
        String sum = "select count(*) from commodity a,classification b,succession c where a.lid = b.lid and" +
                " b.xid = c.xid and b.xid=? and isdefault=1 ";
        Integer total = sqlHelper.one(sum.toString(),rs -> {
            return rs.getInt(1);
        },xid);
        return total;
    }
    public Pager selectdata(int xid, Integer pageNo, Integer pageSize) {
        ArrayList<Object> params = new ArrayList<>();

        String sum = "select count(*) from commodity a,classification b,succession c where a.lid = b.lid and" +
                " b.xid = c.xid and b.xid=? and isdefault=1 ";

        String sql = "select a.* from commodity a,classification b,succession c where a.lid = b.lid and" +
                " b.xid = c.xid and b.xid=? and isdefault=1 limit ?,?";

        params.add(xid);
        params.add((pageNo-1)*pageSize);
        params.add(pageSize);

        Integer total = sqlHelper.one(sum,rs -> {
            return rs.getInt(1);
        },xid);
        List<Commodity> query = sqlHelper.query(sql, new CommodityRowmapper(), params.toArray());

        Pager pager = new Pager(pageNo,pageSize);
        pager.setTotal(total);
        pager.setData(query);
        return pager;
    }

    public Pager shaixuan(String[] strings, Integer pageno, Integer leftspc, Integer rightspc) {
        StringBuilder stringBuilder = new StringBuilder();
        List<Object> params = new ArrayList<Object>();

        if (leftspc!=null&&rightspc!=null){
            stringBuilder.append(" and (a.sprice>? and a.sprice<?)");
            params.add(leftspc);
            params.add(rightspc);
        }else if (leftspc ==null && rightspc!=null){
            stringBuilder.append(" and a.sprice<=?");
            params.add(rightspc);
        }else if (leftspc!=null && rightspc==null){
            stringBuilder.append(" and a.sprice>=?");
            params.add(leftspc);
        }

        for (int i = 0; i < strings.length; i++) {
            if (i==0){
                stringBuilder.append(" where c.xid = ?");
            }else{
                stringBuilder.append(" or c.xid = ?");
            }
            params.add(strings[i]);
        }

        Integer total = sqlHelper.one("select count(*) from commodity a left join classification c on a.lid = c.lid and isdefault=1 "+stringBuilder.toString(),
                rs -> {
                    return rs.getInt(1);
                },params.toArray());
        stringBuilder.append(" limit ?,?");
        params.add((pageno-1)*16);
        params.add(16);
        List<Commodity> query = sqlHelper.query("select a.* from commodity a left join classification c on a.lid = c.lid and isdefault=1 " + stringBuilder.toString(), new CommodityRowmapper(), params.toArray());
        Pager pager = new Pager(pageno, 16);
        pager.setTotal(total);
        pager.setData(query);
        return pager;
    }

//    public Pager pxquery(String[] strings, int pageno) {
//        StringBuilder stringBuilder = new StringBuilder();
//        List params = new ArrayList();
//
//        for (int i = 0; i < strings.length; i++) {
//            if (i==0){
//                stringBuilder.append(" where c.xid = ?");
//            }else{
//                stringBuilder.append(" or c.xid = ?");
//            }
//            params.add(strings[i]);
//        }
//
//        Integer total = sqlHelper.one("select count(*) from commodity a left join classification c on a.lid = c.lid "+stringBuilder.toString(),rs -> {
//            return rs.getInt(1);
//        },params.toArray());
//
//        stringBuilder.append(" order by a.sprice desc limit ? , ?");
//        params.add((pageno-1)*16);
//        params.add(16);
//
//        List<Commodity> query = sqlHelper.query("select a.* from commodity a left join classification c on a.lid = c.lid "+stringBuilder.toString(),
//                new CommodityRowmapper(),params.toArray());
//        Pager pager = new Pager(pageno,16);
//        pager.setTotal(total);
//        pager.setData(query);
//        return pager;
//    }


    class SuccessionRowmapper implements RowMapper<Succession> {

        @Override
        public Succession map(ResultSet rs) throws SQLException {
            return new Succession(rs.getInt("xid"),
                    rs.getString("xname"));
        }
    }

    class CommodityRowmapper implements RowMapper<Commodity> {

        @Override
        public Commodity map(ResultSet rs) throws SQLException {
            return new Commodity(rs.getInt("sid"),
                    rs.getString("sname"),
                    rs.getString("url"),
                    rs.getBigDecimal("sprice"),
                    rs.getBoolean("isdefault"),
                    rs.getInt("lid"));
        }
    }
}
